sql / as sysdba

ARCHIVE LOG LIST;

mkdir -p /u01/app/oracle/fast_recovery_area

chmod 775 /u01/app/oracle/fast_recovery_area

sql / as sysdba

SHOW PARAMETER DB_RECOVERY_FILE

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=30G SCOPE=BOTH;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;

SHOW PARAMETER DB_RECOVERY_FILE

SHUTDOWN IMMEDIATE

STARTUP MOUNT

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

ARCHIVE LOG LIST;

ALTER SYSTEM SWITCH LOGFILE;

cd /u01/app/oracle/fast_recovery_area/DB01/archivelog



Block Change Tracking


sql / as sysdba

SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING;

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata';

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING;

rman target /

SHOW ALL;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

SHOW ALL;




Tworzenie kopii zapasowej

export NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'

BACKUP INCREMENTAL LEVEL 0 DATABASE;

BACKUP INCREMENTAL LEVEL 1 DATABASE;

BACKUP ARCHIVELOG ALL DELETE ALL INPUT;

rman target /

LIST BACKUP;

LIST BACKUP SUMMARY;




Usuwanie zbędnych backupów


DELETE OBSOLETE;

sql / as sysdba

SHOW PARAMETER CONTROL_FILE_RECORD_KEEP_TIME

ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME=30 SCOPE=BOTH;

SHOW PARAMETER CONTROL_FILE_RECORD_KEEP_TIME

DELETE OBSOLETE;





Odtwarzanie bazy


sql / as sysdba

SET HEADING OFF;

SELECT 'rm ' || NAME FROM V$DATAFILE ORDER BY FILE#;

SELECT 'rm ' || NAME FROM V$TEMPFILE;

SELECT 'rm ' || NAME FROM V$CONTROLFILE;

SELECT 'rm ' || MEMBER FROM V$LOGFILE;

SELECT 'rm ' || VALUE FROM V$PARAMETER WHERE NAME = 'spfile';

SHUTDOWN IMMEDIATE

vi /u01/app/oracle/product/23.0.0/dbhome_1/dbs/initdb01.ora

db_name=db01

sql / as sysdba

STARTUP NOMOUNT

rman target /

restore spfile from '/u01/app/oracle/fast_recovery_area/DB01/autobackup/2026_02_07/o1_mf_s_1224598252_nrgggw7v_.bkp';

sql / as sysdba

SHUTDOWN IMMEDIATE

STARTUP NOMOUNT

rman target /

restore controlfile from autobackup;

sql / as sysdba

SHOW PARAMETER CONTROL_FILES

ALTER DATABASE MOUNT;

rman target /

list backup summary;

RUN {
  RESTORE DATABASE;
  RECOVER DATABASE UNTIL AVAILABLE REDO;
}


sql / as sysdba

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SELECT FILE#, NAME, TO_CHAR(CHECKPOINT_CHANGE#) AS NUMER FROM V$DATAFILE_HEADER ORDER BY 3;

ALTER DATABASE OPEN RESETLOGS;

ALTER PLUGGABLE DATABASE ALL OPEN;





Eksportowanie i importowanie danych - Oracle Data Pump


mkdir -p /u01/app/oracle/export

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE DIRECTORY dmp_dir AS '/u01/app/oracle/export';

GRANT READ, WRITE ON DIRECTORY dmp_dir TO system;

EXIT

expdp system/@//localhost:1521/pdb1 schemas=hr directory=dmp_dir dumpfile=schemas_hr.dmp logfile=schemas_hr_export.log

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

DROP USER HR CASCADE;

EXIT

impdp system/@//localhost:1521/pdb1 schemas=hr DIRECTORY=dmp_dir DUMPFILE=schemas_hr.dmp LOGFILE=schemas_hr_import.log

expdp system/@//localhost:1521/pdb1 TABLES=hr.locations,hr.countries,hr.regions DIRECTORY=dmp_dir DUMPFILE=tables.dmp LOGFILE=tables.log

ALTER SESSION SET CONTAINER=PDB1;

SELECT owner, table_name, tablespace_name FROM   dba_tables WHERE  owner = 'HR' AND    table_name IN ('EMPLOYEES','DEPARTMENTS','JOBS') ORDER BY table_name;

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB2;

CREATE TABLESPACE HR_SYSTEM DATAFILE '/u01/app/oracle/oradata/DB01/pdb2/hr_system01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 10G;

CREATE USER HR IDENTIFIED BY Password_1 DEFAULT TABLESPACE HR_SYSTEM TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON HR_SYSTEM;

GRANT CONNECT, DB_DEVELOPER_ROLE TO HR;

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB2;

CREATE DIRECTORY dmp_dir AS '/u01/app/oracle/export';

GRANT READ, WRITE ON DIRECTORY dmp_dir TO system;

EXIT

impdp system/@//localhost:1521/pdb2 TABLES=HR.LOCATIONS,HR.COUNTRIES, HR.REGIONS DIRECTORY=dmp_dir DUMPFILE=tables.dmp REMAP_TABLESPACE=USERS:HR_SYSTEM LOGFILE=tables_import.log

expdp system/@//localhost:1521/pdb1 schemas=hr DIRECTORY=dmp_dir DUMPFILE=schemas_hr_%U.dmp LOGFILE=schemas_hr_export.log PARALLEL=4




